# -*- coding: utf-8 -*-
from bs4 import BeautifulSoup
# !/usr/bin/python
import mysql.connector
import requests

# 股票编码
# 三伏股份
# gpno = "603938"
# min = "15.90"
# max = "24.50"

# 芯能科技
# gpno = "603105"
# min = "11.76"
# max = "18.68"

# 方大炭树
gpno = "600516"
min = "15.79"
max = "22.86"

year = 2019
jidu = 1
#url = 'http://money.finance.sina.com.cn/corp/go.php/vMS_MarketHistory/stockid/%s.phtml?year=%d&jidu=%d' % (gpno, year, jidu)
url = 'http://money.finance.sina.com.cn/corp/go.php/vMS_MarketHistory/stockid/%s.phtml' % gpno
res = requests.get(url)
res.encoding = "utf-8"
html = res.content
soup = BeautifulSoup(html, 'lxml')
# table = soup.find("table", id="FundHoldSharesTable")
# for child in table.descendants:
#    print(child)

# 股票名称
titlelist = soup.title.text.split('(%s)' % gpno)
title = 'no 名字'
if len(titlelist) > 0:
    title = titlelist[0]
print(title)

mydb = mysql.connector.connect(
    host="localhost",
    port="8889",
    user="root",
    password="root",
    database='zztest'
)
mycursor = mydb.cursor()

# 删除表 drop table if exists gp
# 股票名称 表
createGPSql = """create table if not exists gp(id varchar(20)  primary key,
                                                name varchar(50), 
                                                min float,
                                                max float)"""
mycursor.execute(createGPSql)
mydb.commit()

# 创建表 股票数据表
createGPDataSql = """create table if not exists gpdata(id integer auto_increment primary key, 
                                                open float,
                                                finish float, 
                                                min float, 
                                                max float, 
                                                year integer,
                                                jidu integer,
                                                gp_no varchar(20), 
                                                foreign key (gp_no) references gp(id))"""
mycursor.execute(createGPDataSql)
mydb.commit()

# 删除股票数据
mycursor.execute("delete from gpdata where gp_no=%s and year=%d and jidu=%d" % (gpno, year, jidu))
mydb.commit()

# 插入股票数据
# insert into user(id, name)  select 3,'zhh' from dual where not exists(select * from user  where id=1)
# insert into gp(id, name, min, max) select %s,%s,%s,%s from dual where not exists(select * from gp where id=%s)
# gpSql = "insert into gp(id, name, min, max)  values(%s, '%s', %s, %s)" % (gpno, title, min, max)
gpSql = "insert into gp(id, name, min, max) select %s,'%s',%s,%s from dual where not exists(select * from gp where id=%s)" % (gpno, title, min, max, gpno)
mycursor.execute(gpSql)
mydb.commit()

# 数据插入
# 开盘价 2 最高价 3 收盘价 4 最低价 5
trList = soup.select('table[id="FundHoldSharesTable"] tr')
trLength = len(trList)
for i in range(trLength):
#for tr in trList:
    tr = trList[trLength - 1 - i]
    aList = tr.select('a')
    if len(aList) == 0:
        continue
    data = tr.select('td div')
    sql = "insert into gpdata(gp_no, open, finish, min, max, year, jidu) \
                          values('%s', %f, %f, %f, %f, %d, %d)" % (gpno, float(data[1].get_text()),
                                                           float(data[3].get_text()),
                                                           float(data[4].get_text()),
                                                           float(data[2].get_text()),
                                                           year,
                                                           jidu)
    try:
        mycursor.execute(sql)
        print(sql)
        mydb.commit()
    except:
        mydb.rollback()



